from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
class MySqlSession:
    # 创建数据库引擎
    # 请根据实际情况修改数据库连接信息
    engine = create_engine('mysql+mysqlconnector://root:Wit2cloud!24@43.138.226.128:3306/ssc-platform')
    # 创建会话工厂
    Session = sessionmaker(bind=engine)
    session = Session()
    #获取Session
    @classmethod
    def get_session(cls):
        return cls.session



# 定义数据模型
class Article(declarative_base()):
    __tablename__ = 'blog_article'

    # 转换成类的属性：title,author,price,content,pub_date,status
    id = Column(Integer, primary_key=True)
    title = Column(String(200))
    author = Column(String(100))
    price = Column(Integer)
    content = Column(String(200))
    pub_date = Column(String(200))
    status = Column(Integer)
    def __repr__(self):
        return f"<Article(title='{self.title}', author='{self.author}', price={self.price})>"



# 创建表
#Base.metadata.create_all(engine)

# 插入数据
def add_article():
    session = MySqlSession.get_session()
    new_article = Article(title='测试标题', author='测试作者', price=100, content='测试内容', pub_date='2023-05-01', status=0)
    session.add(new_article)
    session.commit()
    # 关闭会话
    session.close()


# 查询数据
def query_all_articles():
    session = MySqlSession.get_session()
    articles = session.query(Article).all()
    for article in articles:
        print(article)
    # 关闭会话
    session.close()

# 更新数据
def update_article():
    session = MySqlSession.get_session()
    article_update = session.query(Article).filter_by(title='Python').first()
    if article_update:
        article_update.status = 1
        session.commit()
    # 关闭会话
    session.close()

# 删除数据
def delete_article():
    session = MySqlSession.get_session()
    article_delete = session.query(Article).filter_by(id=1).first()
    if article_delete:
        session.delete(article_delete)
        session.commit()
    # 关闭会话
    session.close()



